package org.opens.util;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * 功能:
 *
 * @author zym
 * @Date 2021/12/14 17:05
 */
public class ExcelUtil2 {

    private WritableCellFormat titleWcf;//excel标题

    private WritableCellFormat legendWcf;//excel表头

    private WritableCellFormat contentWcf;//excel正文

    public ExcelUtil2() throws WriteException {
        init();
    }

    /**
     * 初始化标题字体、表头字体、内容字体的样式
     * @throws WriteException
     */
    private void init() throws WriteException{
        // 设置标题字体
        WritableFont titleWf = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD,
                false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        this.titleWcf = new WritableCellFormat(titleWf);
        this.titleWcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
        this.titleWcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE, jxl.format.Colour.BLACK);// 设置细边框

        // 设置表头字体
        WritableFont legendWf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD,
                false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        this.legendWcf = new WritableCellFormat(legendWf);
        this.legendWcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置水平对齐方式
        this.legendWcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 设置垂直对齐方式
        this.legendWcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);// 设置细边框
        this.legendWcf.setWrap(true);

        // 设置正文字体
        WritableFont contentWf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD,
                false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        this.contentWcf = new WritableCellFormat(contentWf);
        this.contentWcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
        contentWcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 设置垂直对齐方式
        contentWcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, Colour.BLACK);// 设置细边框
    }

    /**
     * 将已有数据写入Excel
     * @param templateFile 模板文件
     * @param os 数据流，如果是写本地文件的话，可以是FileOutputStream;如果是写Web下载的话，可以是ServletOupputStream
     * @param title 工作簿的标题,如果不用的话,可以写null或者""
     * @param legends 表头名称
     * @param sizes 设定每一列的宽度
     * @param datas 数据集
     * @param offsetX 单元格横向偏移量
     * @param offsetY 单元格纵向偏移量
     * @throws BiffException
     * @throws IOException
     * @throws RowsExceededException
     * @throws WriteException
     */
    public void export(File templateFile, OutputStream os, String title, String[] legends, int[] sizes, List<Object[]> datas, int offsetX, int offsetY) throws BiffException, IOException, RowsExceededException, WriteException {
        WritableWorkbook wbook = null;
        WritableSheet wsheet = null;
        if(templateFile!=null){
            Workbook wb = Workbook.getWorkbook(templateFile);
            wbook = Workbook.createWorkbook(os, wb);
            wsheet = wbook.getSheet(0);
        }else{
            wbook = Workbook.createWorkbook(os); // 建立excel文件
            wsheet = wbook.createSheet("第一页", 0); // sheet名称
        }

        if (title != null && !title.trim().equals("")) {// 添加标题
            int length = datas.isEmpty() ? (legends == null ? 0 : legends.length) : datas.get(0).length;
            wsheet.mergeCells(offsetX, offsetY, offsetX + length - 1, offsetY); // 合并单元格
            Label wlabel = new Label(offsetX, offsetY, title, this.titleWcf);
            wsheet.addCell(wlabel);
        }

        // 如果有标题的话，要设置一下偏移
        int rowIndex = 1;
        if (title == null || title.trim().equals("")) {
            rowIndex = 0;
        }

        //创建表头
        if(legends!=null && sizes!=null){
            rowIndex = rowIndex + this.buildLegend(wsheet, offsetX, offsetY+rowIndex, legends, sizes);
        }

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        WritableCell wcell = null;//单元格
        //往Excel输出数据
        for (int i=0;i<datas.size();i++) {
            for(int j=0;j<datas.get(i).length;j++){
                Object value = datas.get(i)[j];
                if(value instanceof java.lang.Number) {
                    wcell = new Number(offsetX+j, offsetY+rowIndex, ((java.lang.Number)value).doubleValue(), this.contentWcf);
                }else if(value instanceof java.util.Date) {
                    wcell = new Label(offsetX+j, offsetY+rowIndex, sdf.format((java.util.Date)value), this.contentWcf);
                }else{
                    wcell = new Label(offsetX+j, offsetY+rowIndex, (java.lang.String)value, this.contentWcf);
                }
                wsheet.addCell(wcell);
            }
            rowIndex++;
        }

        //创建额外内容
        buildExtraContent(wsheet, offsetX, offsetY+rowIndex);
        wbook.write(); // 写入文件
        wbook.close();
        os.flush();
        os.close();
    }

    public void buildExtraContent(WritableSheet wsheet, int x, int y){
    }

    /**
     * 创建表头
     * @param wsheet
     * @param x
     * @param y
     * @param legends
     * @param sizes
     * @return 表头所在的行数
     * @throws WriteException
     * @throws RowsExceededException
     */
    public int buildLegend(WritableSheet wsheet, int x, int y, String[] legends, int[] sizes) throws RowsExceededException, WriteException {
        for(int i=0; i<legends.length; i++){
            Label wlabel = new Label(x+i, y, legends[i], this.legendWcf);
            wsheet.setColumnView(x+i, sizes[i]);
            wsheet.addCell(wlabel);
        }
        return 1;
    }

}
